USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspValidateLogin]') 
AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspValidateLogin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Validate user credentials
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/06/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @szUserEmail is NULL or empty
	@iRetCode = -3, parameter @szPassword is NULL or empty
	@iRetCode = -4, User not found
	@iRetCode = -5, User has revoke status
	@iRetCode = -6, Store has revoke status
	@iRetCode = -7, parameter @szPassword does not match, secure question has been return
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/06/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------
	declare
		@szUserEmail [nvarchar] (512)
		,@szPassword [nvarchar] (128)
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)
		,@biUserID [bigint]
		,@szSecureQuestion [nvarchar] (512)

	select
		@szUserEmail = 'admin@ppj.com'
		,@szPassword = 'AdminPPJ'

	exec [svc].[uspValidateLogin]
		@szUserEmail
		,@szPassword
		,@iRetCode output 
		,@szMsgTier1 output   
		,@szMsgTier2 output  
		,@biUserID output 
		,@szSecureQuestion output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg]   
		,@biUserID as [UserID] 
		,@szSecureQuestion as [SecureQuestion]  
*/

CREATE PROCEDURE [svc].[uspValidateLogin]
	@szUserEmail [nvarchar] (512)
	,@szPassword [nvarchar] (128)
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
	,@biUserID [bigint] output 
	,@szSecureQuestion [nvarchar] (512) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspValidateLogin')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

declare @bRevokeStatus [bit],
	@szTmpPassword [nvarchar] (128),
	@biTmpUserID [bigint],
	@szTmpSecureQuestion [nvarchar] (512),
	@bStoreRevokeStatus [bit]

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
	,@biUserID = 0
	,@bRevokeStatus = 1
	,@szTmpPassword = ''
	,@biTmpUserID = 0
	,@szSecureQuestion = ''
	,@bStoreRevokeStatus= 1

-- Validate parameters
if coalesce(@szUserEmail,'')=''
-- Parameter is NULL or empty
	set @iRetCode = -2
else if coalesce(@szPassword,'')=''
	set @iRetCode = -3
else
 begin
	select @biTmpUserID = u.UserID, 
		@bRevokeStatus = u.RevokeStatus,
		@szTmpPassword = u.[Password],
		@szTmpSecureQuestion = u.SecureQuestion,
		@bStoreRevokeStatus = s.RevokeStatus
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserEmail = @szUserEmail
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		set @iRetCode = -4
	 end
	else if coalesce(@bRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -5
	 end
	else if coalesce(@bStoreRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -6
	 end
	else if @szTmpPassword <> @szPassword COLLATE SQL_Latin1_General_CP1_CS_AS
	 begin
-- password does not match	 
		select @iRetCode = -7,
				 @szSecureQuestion = @szTmpSecureQuestion
	 end
	else
	 begin
		select @biUserID = @biTmpUserID

		-- do we have outer transaction?
		SET @TranCounter = @@TRANCOUNT;
		IF @TranCounter > 0
		  SAVE TRANSACTION uspValidateLogin;
		else
		  BEGIN TRANSACTION;

		update dbo.tblUser set DateLastLogin = GETDATE()
			where UserID = @biUserID

		-- commit transaction
		if @TranCounter = 0 -- no outer transaction
		  COMMIT TRANSACTION;
	 end 
 end

if @iRetCode <> 0
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = 

ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = 

ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspValidateLogin;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biTmpUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END

GO


------------------------------------------------------
-- drop table [dbo].[tblEmailTemplate]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblEmailTemplate](
	[EmailTemplateID] [int] IDENTITY(1,1) NOT NULL,
	[TemplateName] [varchar] (64) NOT NULL,
	[Subject] [nvarchar] (512) NOT NULL,
	[Template] [varchar] (max) NOT NULL,
	[EmailFormat] [varchar] (10) not null,
	[RevokeStatus] [bit] NOT NULL,
	[DateRevoked] [datetime] NULL,
	[DateCreated] [datetime] NOT NULL,
	[UserCreated] [bigint] NOT NULL,
	[DateUpdated] [datetime] NULL,
	[UserUpdated] [bigint] NULL,
 CONSTRAINT [PK_tblEmailTemplate] PRIMARY KEY CLUSTERED 
(
	[EmailTemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblEmailTemplate] ADD  DEFAULT ('text') FOR [EmailFormat]
GO

ALTER TABLE [dbo].[tblEmailTemplate] ADD  DEFAULT ((0)) FOR [RevokeStatus]
GO

ALTER TABLE [dbo].[tblEmailTemplate] ADD  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[tblEmailTemplate] ADD  DEFAULT ((0)) FOR [UserCreated]
GO

insert into [dbo].[tblEmailTemplate] ([TemplateName],[Subject],[EmailFormat],[Template])
values ('ChangePassword','Password has been changed','html',
'<html>
<head>
<title>CBM - Customer Billing Module--Development Environment</title>
<style type="text/css">
a:link, a:visited { color: #3b3a33; text-decoration: underline; }
a:active, a:hover { color: #3b3a33; text-decoration: none; }
body { background-color: #ffffff; color: #3b3a33; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; margin: 4px 4px 4px 4px; }
td { color: #3a5333; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: 12px; padding: 10px 10px 10px 10px; border: 1px solid #3A5333;}
td.green { background-color: #3a5333; color: #ffffff; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: 12px; padding: 10px 10px 10px 10px; border: 1px solid #3A5333;}
</style>
</head>

<body>
  <table width="400" cellspacing="0" cellpadding="0" border="0">
    <tr>
    <td class="green">PopUpJoe - application--Development Environment notification</td>
    </tr>
    <tr>
    <td><b>Account update</b><br><br>Your personal information on PopUpJoe - application--Development Environment was changed on [dt].
    <br><br>Your username is your email address and <br>Your new password is: <b>[pwd]</b>
    <br><br>Please access PopUpJoe - application--Development Environment at <a href="http://198.101.233.96">http://www.ppj.com</a><br><br>
    <i>This is an automated email. Please do not reply.</i></td>
    </tr>
  </table>
</body>
</html>')

--select * from [dbo].[tblEmailTemplate]
-------------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspSendEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspSendEmail]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		
-- Create date: 
-- Description:	Sends email based on recipient key
-- =============================================
CREATE PROCEDURE [dbo].[uspSendEmail] 
	@szSource [varchar](100) = NULL,
	@szEmailSubject [nvarchar](512),
	@szEmailDetails [nvarchar](max),
	@szRecipientListAddresses [nvarchar](max),
	@szSQLQueryString [nvarchar] (max),	/* query string to email */
	@iUserID [int] = 0,
	@iQResultWidth [int] = 125,			/* for query result */
	@szEmailFormat [varchar] (10) = 'Text', /* type of email body Text/HTML*/
	@iRetCode [int] OUTPUT,
	@bRecordOnly [bit] = 1
AS
/*
	@iRetCode :
		0 sucessful;
		-1 No Recipient List is empty
		-2 Database profile record not found
		-3 Database profile is empty
		-4 Failed to send message inside of call to sp_send_dbmail
*/
BEGIN
	SET NOCOUNT ON

	Declare @iMessageKey [int],
		 @szMsg [varchar] (max),
		 @iMailResult [int],
		 @szDBMailProfileName [nvarchar] (255),
		 @szCurrentDBName [nvarchar] (128),
		 @bAttachResult [bit],
		 @szTmpSource [varchar] (100)

	select @iRetCode = 0
	
	--Clean up parameters
	select	@szEmailSubject		= coalesce(@szEmailSubject,''),
			@szEmailDetails	= coalesce(@szEmailDetails, ''),
			@szSQLQueryString = coalesce(@szSQLQueryString,''),
			@szRecipientListAddresses = coalesce(@szRecipientListAddresses,''),
			@szTmpSource = 'uspSendEmail',
			@iUserID = coalesce(@iUserID,0),
			@bRecordOnly = coalesce(@bRecordOnly,0)
	
	--Log and fail if no addresses stored for the recipient key list
	if len(rtrim(@szRecipientListAddresses)) = 0
		begin
			--select @szMsg = 'No addresses stored for recipient list key [' + @pRecipientListKey
			--		+ '].  The intended message follows.'
			--		+ char(10) + char(13) + 'Subj: ' + rtrim(@szEmailSubject)
			--		+ char(10) + char(13) + 'Message: ' + rtrim(@szEmailDetails)

			--insert into dbo.tblEventLog (BatchKey, ProcessKey, EventMsg, EventDetails, EventSource)
			--values (@pBatchKey,@pProcessKey,'No addresses stored for recipient list key [' + @pRecipientListKey
			--		+ '].',@szMsg,@szTmpSource)

			Select @iRetCode = -1
			Return
		end

	--Retrieve DB mail profile name
	select @szDBMailProfileName = LookUpShortName
		from [dbo].[vwLookUp] (nolock)
		where LookUpName = 'DBMail' and LookupSubType = 1
	
	--Log and fail if no match to recipient key list
	if @@rowcount = 0
		begin
			--select @szMsg = 'No DBMail profile record found when '
			--		+ 'attempting to send the following message.'
			--		+ char(10) + char(13) + 'Subj: ' + rtrim(@szEmailSubject)
			--		+ char(10) + char(13) + 'Message: ' + rtrim(@szEmailDetails)

			--insert into dbo.tblEventLog (BatchKey, ProcessKey, EventMsg, EventDetails, EventSource)
			--values (@pBatchKey,@pProcessKey,'No tblSettings record found when '
			--		+ 'attempting to send the following message.',@szMsg,@szTmpSource)

			Select @iRetCode = -2
			Return
		end

	--Log and fail if no profile name is stored
	if len(rtrim(@szDBMailProfileName)) = 0
		begin
			--select @szMsg = 'DBMail profile was empty when '
			--		+ 'attempting to send the following message.'
			--		+ char(10) + char(13) + 'Subj: ' + rtrim(@szEmailSubject)
			--		+ char(10) + char(13) + 'Message: ' + rtrim(@szEmailDetails)

			--insert into dbo.tblEventLog (BatchKey, ProcessKey, EventMsg, EventDetails, EventSource)
			--values (@pBatchKey,@pProcessKey,'tblSettings.DBMailProfileName was empty when '
			--		+ 'attempting to send the following message.',@szMsg,@szTmpSource)

			Select @iRetCode = -3
			Return
		end

	--All validations passed
	select @bAttachResult = case when len(@szSQLQueryString) = 0 then 0 else 1 end;
	select @szCurrentDBName = DB_NAME(dbid) from master.dbo.sysprocesses WHERE spid = @@SPID;

	--Add entry to messages table
	insert into [dbo].[tblEmailLog](
		[Source],
		[EmailSubject],
		[EmailDetails],
		[RecipientList],
		[DateCreated],
		[UserCreated])	
	values (@szSource,
		@szEmailSubject,
		@szEmailDetails,
		@szRecipientListAddresses,
		GETDATE(),
		@iUserID)

	--Remember new key
	Select @iMessageKey = scope_identity()

	--Physically send the message
	if @bRecordOnly = 0
	 begin
		execute @iMailResult = msdb.dbo.sp_send_dbmail 
				@Profile_Name = @szDBMailProfileName, 
				@recipients = @szRecipientListAddresses, 
				@Subject = @szEmailSubject,
				@execute_query_database = @szCurrentDBName,
				@Body = @szEmailDetails,
				@body_format = @szEmailFormat,
				@query = @szSQLQueryString,
				@query_result_width = @iQResultWidth,
				@query_result_header = 1,
				@attach_query_result_as_file = @bAttachResult;

		if @iMailResult = 0
		begin
			Update dbo.tblEmailLog set [DateSend] = GetDate() where EmailLogID = @iMessageKey
			Select @iRetCode = 0
		end
		else
		begin
			Select @iRetCode = -4
			Return
		end

	 end
	else
	 begin
		Select @iRetCode = 0
	 end 
	--Only update the sent date if the message appears to have been sent

	Return
END
GO

-----------------------------------------------------------



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspValidateSecureAnswer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspValidateSecureAnswer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Validate user's answer on secure question
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/06/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @szUserEmail is NULL or empty
	@iRetCode = -3, parameter @szSecureAnswer is NULL or empty
	@iRetCode = -4, User not found
	@iRetCode = -5, User has revoke status
	@iRetCode = -6, Store has revoke status
	@iRetCode = -7, parameter @szSecureAnswer does not match with secure answer
	@iRetCode = -8, Email template not found
	@iRetCode = -9, error in [dbo].[uspSendEmail]
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/06/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------
	declare
		@szUserEmail [nvarchar] (512)
		,@szSecureAnswer [nvarchar] (512)
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select
		@szUserEmail = 'admin@ppj.com'
		,@szSecureAnswer = 'test'

	exec [svc].[uspValidateSecureAnswer]
		@szUserEmail
		,@szSecureAnswer
		,@iRetCode output 
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   

*/

CREATE PROCEDURE [svc].[uspValidateSecureAnswer]
	@szUserEmail [nvarchar] (512)
	,@szSecureAnswer [nvarchar] (512)
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspValidateSecureAnswer')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

declare @bRevokeStatus [bit],
	@szTmpPassword [nvarchar] (128),
	@biTmpUserID [bigint],
	@szTmpSecureAnswer [nvarchar] (512),
	@bStoreRevokeStatus [bit]

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
--	,@biUserID = 0
	,@bRevokeStatus = 1
	,@szTmpPassword = ''
	,@biTmpUserID = 0
--	,@szSecureQuestion = ''
	,@bStoreRevokeStatus= 1

-- Validate parameters
if coalesce(@szUserEmail,'')=''
-- Parameter is NULL or empty
	set @iRetCode = -2
else if coalesce(@szSecureAnswer,'')=''
	set @iRetCode = -3
else
 begin
	select @biTmpUserID = u.UserID, 
		@bRevokeStatus = u.RevokeStatus,
		@szTmpPassword = u.[Password],
		@szTmpSecureAnswer = u.SecureAnswer,
		@bStoreRevokeStatus = s.RevokeStatus
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserEmail = @szUserEmail
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		set @iRetCode = -4
	 end
	else if coalesce(@bRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -5
	 end
	else if coalesce(@bStoreRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -6
	 end
	else if @szTmpSecureAnswer <> @szSecureAnswer -- COLLATE SQL_Latin1_General_CP1_CS_AS
	 begin
-- answer does not match	 
		select @iRetCode = -7
	 end
	else
	 begin
-- answer match, send email with existed password				
		declare @szEmailTemplate [varchar] (max),
			@szSource [varchar](100),
			@szEmailSubject [nvarchar](512),
			@szEmailDetails [nvarchar](max),
			@szRecipientListAddresses [nvarchar](max),
			@szSQLQueryString [nvarchar] (max),
			@iTmpUserID [int],
			@iQResultWidth [int],
			@szEmailFormat [varchar] (10),
			@iTmpRetCode [int],
			@bRecordOnly [bit],
			@szDateTime [varchar] (30)

		declare @szYear [varchar] (4),
			@szDate1 [varchar] (30),
			@szDate2 [varchar] (30),
			@iPos [int]
		
		select @szEmailTemplate = [Template],
			@szEmailSubject = [Subject],
			@szEmailFormat = [EmailFormat] 
			from dbo.tblEmailTemplate (nolock) 
			where TemplateName = 'ChangePassword'
			and RevokeStatus = 0

		if coalesce(@szEmailTemplate,'')=''
		 begin
-- email template not found
			select @iRetCode = -8		 
		 end
		else
		 begin
				
			select @szYear = CAST(year(getDate()) as [varchar] (4)),
				@szDate1 = convert(varchar, getdate(), 100),
				@iPos = charindex(@szYear,@szDate1)+5,
				@szDate2 = CONVERT(varchar,getdate(),101)+' at '+
				ltrim(SUBSTRING(@szDate1,@iPos,len(@szDate1)-@iPos+1))		 
				
			select @szEmailTemplate = replace(replace(@szEmailTemplate,'[pwd]',@szTmpPassword),'[dt]',@szDate2)

			select @szEmailDetails = @szEmailTemplate,
				@szSource = @szProcessName,
				@szRecipientListAddresses = @szUserEmail

			exec [dbo].[uspSendEmail]
				@szSource,
				@szEmailSubject,
				@szEmailDetails,
				@szRecipientListAddresses,
				@szSQLQueryString,
				@iTmpUserID,
				@iQResultWidth,
				@szEmailFormat,
				@iTmpRetCode output
			
			if @iTmpRetCode <> 0
			 begin
				select @iRetCode = -9
			 end
		 end	
	 end 
 end

if @iRetCode <> 0
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspValidateLogin;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biTmpUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

-------------------------------------------------------------
 
-- select * from dbo.tblMsgOutput

insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspValidateSecureAnswer',-1, 'SQL update error',202
union all
select 'uspValidateSecureAnswer',-2, 'parameter @szUserEmail is NULL or empty',202
union all
select 'uspValidateSecureAnswer',-3, 'parameter @szSecureAnswer is NULL or empty',202
union all
select 'uspValidateSecureAnswer',-4, 'User not found',202
union all
select 'uspValidateSecureAnswer',-5, 'User has revoke status',202
union all
select 'uspValidateSecureAnswer',-6, 'Store has revoke status',202
union all
select 'uspValidateSecureAnswer',-7, 'parameter @szSecureAnswer does not match with secure answer',202
union all
select 'uspValidateSecureAnswer',-8, 'Email template not found',202
union all
select 'uspValidateSecureAnswer',-9, 'error in [dbo].[uspSendEmail]',202

go

-----------------------------------------------------------------------
truncate table [dbo].[tblStore]
go
insert into [dbo].[tblStore](
	[eCommerceType],
	[StoreName],
	[PostalName],
	[Address1],
	[Address2],
	[City],
	[StateProvince],
	[PostalCode],
	[CountryCode],
	[ParentStoreID],
	[StoreNode],
	[SessionExpiration],
	[ContractExpirationDate]
)
values (
	0,
	'PopUpJoe',
	'PopUpJoe LLC',
	'216 Maspeth Avenue',
	NULL,
	'Brooklyn',
	'NY',
	'11211',
	'USA',
	0,
	cast('/' as hierarchyid),
	0,
	'2070-01-01'
)
go
--------------------------------------------------------------------------------------
